Implementing the relational model

We’ve gone quite deep into how you might store data and store only once. The theory was extremely useful for making sure that you hadn’t made any mistakes and the design of how you were going to put things into some kind of structured format was gonna be consistent and well thought out, having a basis in mathematics and set theory. The question arose after this of how you would take this theory and implement it so that you had a a real world way of using the data.

This is where we come to the invention of SQL, which was originally quite primitive and has only ever implemented some of the theory and logic behind it. SQL is a cut down and pragmatic implementation that a lot of academics get upset by, but it’s always been good enough to get the job done. We also have to address the elephant in the room which is computing in the 1960s and 70s was quite primitive, you had things like basic types of strings and numbers of various sorts but the most sophisticated items from the model where you would have a domain that defined something that might use a string, but in a particular way, or perhaps a number. Validation had to be done in your code the database itself couldn’t implement them. Policing the relationships was one thing, but granular validation on data itself was not available in very early systems and still isn’t in systems that don’t let you define types.

Eventually, SQL became a standard, at least in terms of the query language. Most vendors had syntax of their own that would create tables and indexes and so on, but that actually didn’t matter very much because most of the time you were writing queries. If you look at what came before SQL, there were systems like Codasyl where your database was defined as a fixed set of relationships with pointers to different places on disc. This meant that if you wanted to interrogate the database, you would have to get somebody to write you a COBOL program. The beauty of the relational model and it’s flawed but useful child, SQL, was that as long as you’d set your data up correctly so that it had no holes in it you could ask arbitrary questions.

It’s no accident that a lot of the early research into creating RDBMS was funded by organisations like the CIA. They wanted to be able to take quantities of relatively arbitrary data they held about individuals and then look for connections so they could build up a picture of who might not want their country robbed at gunpoint so they could do the CIA thing to them. Indeed, when I worked for Oracle in the early 2000s one of our customers had a whole system of not trading with unethical companies and it was touch and go whether we would be able to do business with them. To my lasting embarrassment I was politically quite naive and thought this an odd thing. Now, of course, trying to make sure you do business that’s ethical is a given in most circumstances that don’t involve the British government. You’d have the same problem with IBM or Microsoft to be honest, it’s the way our system works. Big companies are big because they aren’t ethical.

Another way of storing and retrieving data was simply to use plain files that had indexes on them. This is known as ISAM, which is an acronym for index sequential access method. These files had the data in them, and key fields in those files would have indexes defined which allowed you to quickly get to the places in the file where the data you wanted was stored. This concept is fundamental to what goes on inside an RDBMS. When we start turning theoretical things such as relations into SQL tables and constructing our database the RDBMS is in essence sitting on top of ISAM systems that will be used to physically implement each relation.

Running a query would be translated into pulling data from the tables and then constructing temporary tables that you can then start merging and filtering together, assembling it into your projected and joined result. So the RDBMS turns your SQL into operations on physical structures on disc that look like ISAM files underneath and then assembles that result for you. This is a very key idea: it’s how the abstractions are turned into something where you can use fully get data back. It’s also how you can break queries down to work out why you have performance problems. Once you’ve understood that the table is a bunch of data gathered together and that data has indexes on it to help you quickly find things like a particular customer, or order, or whatever arbitrary data you want, then tuning queries becomes obvious. You can start asking yourself questions like do I need to put an index in a particular place or does the order the tables are in this in the select from part of my query matter? To be fair, a decent optimiser will order the tables correctly anyway as long as the meta data needed to build the query is reasonably up-to-date. That kind of thing that used to matter in the early days. We’ll look at this in a lot more detail later in a section on how queries are constructed, this is a very high-level view.

A word about keys and modern ORM systems

It seems like a lifetime ago, but if you look at the model in its exact form, you notice that we don’t have a column called ID everywhere that has an integer or some arbitrary generated UUID string in it. We started doing this thing with IDs as a pragmatic response to the real world hitting purity of the theory.

In the real world you might have a dozen customers called Singh, they are different people, but it’s possible that you would be unable to create a compound key for your customer based on their name that uniquely identifies them. Including their home address would be an implementation nightmare every time the address changed. You can see how hard this might make life difficult. In this circumstance you’d end up with one row for all of those customers which wouldn’t work so the IDs you see everywhere are in fact something called surrogate keys but everyone seems to have forgotten this.

The other thing surrogate keys allowed you to do was take the actual keys identified in your analysis which might be strings or even combinations of several columns and be able to edit them to whatever you liked without having to cascade that change across the whole system where the references to the key are set. Sticking in IDs everywhere was a pragmatic response to this problem too.

This means that when identifying complicated compound keys in your analysis you should make sure there are at least unique indexes on those compound keys or you’re gonna end up with duplicate records. This is something everybody seems to have forgotten. It’s one of those avoidable messes that is now missed because everyone’s in such a hurry.

ORM is object relational mapping, it’s how we go from our object oriented systems like Ruby to creating SQL that brings us data back. In active record every table has a primary key which is a surrogate key, this is true even of tables that are joining tables that just consist of keys themselves. This means that you can sometimes have joining tables that have duplicates in which can cause all kinds of fun when you’re writing queries. It can do things like double the count of things because of the way the operators combine sets. We’ll get to this later, but putting unique index is on your compound keys in joining tables is also a very good idea, and will speed up the joining process too so there’s no need to stint on indexes in this case. It’s something you should check when physically implementing a database.

Holy databases, Batman!

Some useful ideas:

• Cartesian product. If you were to create a query with no joins it will give you back every row of every table put together in one big projection of all available columns. This is from set theory and if you want to bend your brain look at the Wikipedia entry.
• Fan Trap. If you have a table that has two (or more) 1:many relations and you were to try to get a sum from the many tables you will find that there might be an underlying cartesian product that creates a much bigger combined set before the values are summed and they will be wrong. For example you have customers, orders, order_lines and sales targets. If you were to write a naive query that joins and sums the tables together you will include the sales target data for every row you have in order lines, this will give you the wrong totals.
• Chasm Trap. Where the fan trap is too broad, a chasm trap is too narrow. For example, you want all customers that have a target set, but some of those customers won’t have orders. If you just join the tables together you will not see the ones with targets but no orders. This is what outer joins give you, all the filtered from one table and any data that matches the join conditions but with blank columns for the data that isn’t there.